Query Optimization: An Intelligent Hybrid Approach using Cuckoo and Tabu Search

Query optimization is an important aspect in designing database management systems, aimed to find an optimal query execution plan so that overall time of query execution is minimized. Multi join query ordering (MJQO) is an integral part of query optimizer. This paper aims to propose a solution for MJQO problem, which is an NP complete problem. This paper proposes a heuristic based algorithm as a solution of MJQO problem. The proposed algorithm is a combination of two basic search algorithms, cuckoo and tabu search. Simulation shows some exciting results in favour of the proposed algorithm and concludes that proposed algorithm can solve MJQO problem in less amount of time than the existing methods. DOI: 10.4018/jiit.2013010103 International Journal of Intelligent Information Technologies, 9(1), 40-55, January-March 2013 41 Copyright © 2013, IGI Global. Copying or distributing in print or electronic forms without written permission of IGI Global is prohibited. the execution cost of each of them to choose the QEP having lowest cost. Optimal query plan selected by query optimizer is forwarded to query execution engine which is responsible for execution of query. Query execution engine uses the QEP which is forwarded by query optimizer. Query optimizer is the most critical step in query evaluation; it decides the execution time and the space complexity of query. Query optimization is itself very complex and expensive; its computational complexity is determined by the number of alternatives for QEPs that must be evaluated before deciding the best query execution plan (Matysiak, 1995), The alternative planes grow exponentially with the increase in number of relations involved in a query. In past three decades this problem is addressed in many ways (Jarke & Koch, 1984; Swami & Gupta, 1988; Horng, Kao, & Liu, 1994; Matysiak, 1995; Steinbrunn, Moerkotte, & Kemper, 1997). The join operator (Ribeiro, Ribeiro, & Lanzelotte, 1997) relates two tables through their common attributes. Evaluation of a join operation requires the matching of all tuples of relations according to their join attributes (Ribeiro, Ribeiro, & Lanzelotte, 1997). Cosar, Lim, & Srivastava (1995) shows reordering helps to improve the performance of multi query optimization algorithms. So, by reordering the join, query optimizer can lower the cost of execution of the query has join operator in between several tables. First task for a query optimizer is to decide the order of joins, which is called a multi join query optimization, or ordering problem. The multi join ordering is a combinatorial optimization problem (Dong & Liang, 2007) and if the number of input relations and joins are not fixed it is an NP hard problem (Zhou, 2007). In traditional databases, the total number of relations in multi join queries is usually less than 10 which can be handled by dynamic programming approaches effectively (Li, Liu, Dong, & Gu, 2008). Nowadays the complexity of this problem increases due to the generation of complex multi join queries in some modern applications, such as knowledge base systems, decision support systems, expert systems, OnLine Analytical Processing (OLAP) and data mining etc. Sometimes, the generated query has more than 100 tables in a join (Li, Liu, Dong,